Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Isolate Sequential I/Os

Isolating the sequential I/Os is very important—even with a disk array. As you learned in Chapter 14, “Advanced Disk I/O Concepts,” a sequential access to a disk reduces the time spent seeking between tracks. The seek time can be a large percentage of the time it takes to do an I/O.

By reducing the head movement, you can get both a higher throughput on the disks and a faster response time—both of which are important in an RDBMS environment. Here is a partial list of the things that are handled sequentially in an RDBMS system:

  Log writes. The LGWR writes to the redo log files in a purely sequential fashion.
  Archive log reads and writes. The archive log process reads from the redo log files sequentially and writes to the archive log files sequentially.
  Import/export. The import process reads from the import file in a sequential fashion; the export process writes to the export file in a sequential fashion.
  Database load. The SQL*Loader utility reads the load file sequentially, writes to the database sequentially, and writes to the index sequentially.
  Report files. In the case of a detailed analysis report, the report file generated can be many megabytes in size and is written sequentially.
  Index creation. Creating an index is somewhat sequential; however, chained and migrated rows break up some of the sequential nature of the table scan.
  Decision support activities. Decision support activities such as supply and demand reporting and market studies typically involve large table scans that are mostly sequential.
  Report processing. Report processing typically involves large table scans that are mostly sequential.

Full-table scans are mostly sequential in nature. Chained and migrated rows cause the sequential nature of the scan to be broken up; so does fragmentation of the database. As these problem areas are reduced or eliminated, the table scans become more sequential.

By isolating the sequential I/Os to a particular disk volume, the performance of that volume can be quite high. However, anything that causes random I/Os on the sequential volumes eliminates those performance gains.

To take best advantage of space, you can use the OS volume as a sequential volume if you determine that the system is used only for RDBMS processing, if very few OS activities produce I/Os on that volume, and if the system does not page or swap (that is, if the page/swap file is on the same volume).

Distribute Random I/Os

You should distribute random I/Os across as many physical disks as possible. Doing so spreads out the load among many disks and enables the disk array to process as many random I/Os as those disks allow. If a single disk in the array can handle 50 random I/Os per second, it is reasonable to assume that an array of four similar disks can handle 200 random I/Os per second.

When using a disk array, you do not have to split different types of data files among different logical volumes. In fact, you should do the opposite. When using a disk array, put all the data files—and their indexes—on the same logical volume. The exception to this guideline is a decision support or batch processing system.

The reason it has always been recommended that indexes and data files be separated was to allow for simultaneous accesses. But simultaneous access is what disk arrays excel at. In fact, the more simultaneous accesses you make to the disk array (until saturation), the better the overall throughput.


TIP:  It is still a good idea to separate indexes and data into separate data files for ease of management, but put them both on the same logical volume for performance.

Load balancing of different data files is also unnecessary when using a disk array. It has always been a difficult task for the database administrator to try to balance each data file so that the I/O rates for each disk drive are balanced. Usually, that task is ineffective because of the changing nature of the database workload.

By placing all the data files and indexes across a large striped volume, you are guaranteed to get a better load balance than you can get by hand. In a striped disk configuration, all disks average the same number of I/Os per second, regardless of the configuration and workload.

Earlier, I mentioned that the exception to the rule that data and indexes should be configured on the same logical volume is with the decision support and batch processing systems. Decision support and batch processing typically involve bulk loads and batch updates. A typical scenario is for data to be extracted from the production OLTP systems on a regular basis and loaded onto the DSS (decision support system).

During the database load (which is usually time critical), these four sequential operations may happen simultaneously:

  Reading from the load file
  Writing to the redo log files
  Writing to the data files
  Writing to the index files

In a DSS or batch processing system, it is a good idea to separate the data and index files on separate logical volumes for load purposes.

The data generated from a detailed report file can also cause a significant amount of sequential I/O. If the batch report is the only job being run on the system, the report can be written to the same volume as the redo log file or to the batch load volume because neither of these are active during the batch processing job.

By configuring a DSS system as just described, you lose some performance when you run queries because the load is not as well balanced as it would have been if the indexes and data shared the same drives. This minor performance loss is well made up for by the fact that loading is significantly faster.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.